package com.mk.sql.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author xuzn @ClassName: ReadExcelUtil @ProjectName generator_sql
 * @date 2020/5/20/10:32 下午
 */
public class ReadExcelUtil {
  /**
   * @param filepath            //文件路径
   * @param filename            //文件名
   * @param startrow            //开始行号
   * @param startcol            //开始列号
   * @param sheetnum            //sheet
   */
  @SuppressWarnings("deprecation")
  public static List<Map<String, Object>> readExcel(
      String filepath, String filename, int startrow, int startcol, int sheetnum) {
    List<Map<String, Object>> varList = new ArrayList<Map<String, Object>>();
    try {
      File target = new File(filepath, filename);
      FileInputStream fi = new FileInputStream(target);
      Workbook wb = null;
      if (isExcel2003(filepath)) {
        wb =new HSSFWorkbook(fi);
      }else {
        wb = new XSSFWorkbook(fi);
      }
      // sheet 从0开始
      Sheet sheet = wb.getSheetAt(sheetnum);
      // 取得最后一行的行号
      int rowNum = sheet.getLastRowNum() + 1;
      // 行循环开始
      for (int i = startrow; i < rowNum; i++) {
        Map<String, Object> map = new HashMap<String, Object>();
        // 行
        Row row = sheet.getRow(i);
        // 每行的最后一个单元格位置
        int cellNum = row.getLastCellNum();
        // 列循环开始
        for (int j = startcol; j < cellNum; j++) {
          Cell cell = row.getCell(Short.parseShort(j + ""));
          String cellValue = null;
          if (null != cell) {
            // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
            switch (cell.getCellType()) {
              case 0:
                cellValue = String.valueOf((int) cell.getNumericCellValue());
                break;
              case 1:
                cellValue = cell.getStringCellValue();
                break;
              case 2:
                cellValue = cell.getNumericCellValue() + "";
                break;
              case 3:
                cellValue = "";
                break;
              case 4:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
              case 5:
                cellValue = String.valueOf(cell.getErrorCellValue());
                break;
            }
          } else {
            cellValue = "";
          }
          map.put("var" + j, cellValue);
        }
        varList.add(map);
      }
    } catch (Exception e) {
      System.out.println(e);
    }
    return varList;
  }

  public static boolean isExcel2003(String filePath) {
    return filePath.matches("^.+\\.(?i)(xls)$");
  }

  public static boolean isExcel2007(String filePath) {
    return filePath.matches("^.+\\.(?i)(xlsx)$");
  }
}
